﻿using Corina.Datamodel.Base;
using Corina.Datamodel.Entities;
using Corina.Enumerations;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace Corina.DataContext.Service
{
    public class OverproofNotifyConfigService : ServiceBase
    {
        public DataTable GetAllItems(string connectionString, int userId, int specimenKindId, string measureObject, int index, int size, ref int count)
        {
            List<SqlParameter> paras = new List<SqlParameter>();
            string qstring = "SELECT * FROM ( ";
            string cstring = "SELECT COUNT(*) FROM ( ";
            StringBuilder builder = new StringBuilder();

            builder.Append("SELECT ROW_NUMBER() OVER (ORDER BY tonc.Id DESC) AS ROW_NUMBER, tonc.*, tu.Name AS UserName, tsk.Title ");
            builder.Append("FROM t_overproof_notify_config AS tonc ");
            builder.Append("LEFT JOIN CORINA_ACCO.dbo.t_user AS tu ON tu.Id = tonc.UserId ");
            builder.Append("LEFT JOIN CORINA_PROD.dbo.t_specimen_kind AS tsk ON tsk.Id = tonc.SpecimenKindId ");
            builder.Append("WHERE 1=1 ");
            #region Condition
            builder.Append("AND tonc.MeasureObject LIKE @MeasureObject ");
            paras.Add(new SqlParameter("@MeasureObject", string.Format("%{0}%", measureObject)));

            if (userId > 0)
            {
                builder.Append("AND tonc.UserId = @UserId ");
                paras.Add(new SqlParameter("@UserId", userId));
            }

            if (specimenKindId > 0)
            {
                builder.Append("AND tonc.SpecimenKindId = @SpecimenKindId ");
                paras.Add(new SqlParameter("@SpecimenKindId", specimenKindId));
            }
            #endregion

            builder.Append(") AS query ");

            count = DbOperator.GetCount(connectionString, string.Format("{0}{1}", cstring, builder), paras.ToArray());

            builder.Append("WHERE query.[ROW_NUMBER] BETWEEN @Index + 1 AND @Index + @Size ");
            paras.Add(new SqlParameter("@Index", (index - 1) * size));
            paras.Add(new SqlParameter("@Size", size));
            builder.Append("ORDER BY query.[ROW_NUMBER]");

            DataTable table = DbOperator.GetDataTable(connectionString, string.Format("{0}{1}", qstring, builder), paras.ToArray());
            return table;
        }

        public bool RemoveById(string connectionString, int id, int userId)
        {
            string queryString = "DELETE FROM t_overproof_notify_config WHERE UserId=@UserId AND Id=@Id ";

            List<SqlParameter> paras = new List<SqlParameter>();

            paras.Add(new SqlParameter("@UserId", userId));
            paras.Add(new SqlParameter("@Id", id));

            return DbOperator.ExexuteCommand(connectionString, new List<string>() { queryString }, paras.ToArray()) > 0;
        }

        public bool Insert(string connectionString, OverproofNotifyConfigInfo entity)
        {
            if (DbOperator.Exists<OverproofNotifyConfigInfo>(connectionString, new List<string>() { "SpecimenKindId", "MeasureObject" }, new List<object>() { entity.SpecimenKindId, entity.MeasureObject }))
            {
                return false;
            }
            return DbOperator.UpdateEntity(connectionString, entity, EntityOperation.Insert);
        }
    }
}
